package com.bw.mapper;

import com.bw.bean.*;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface VoMapper {
    @Select("select\n" +
            "    ci.chapter_name `name`,\n" +
            "    count(ucp.chapter_id) `num1`,\n" +
            "    sum(ucp.position_sec) `time`,\n" +
            "    count(distinct ucp.user_id) `num2`,\n" +
            "    sum(ucp.position_sec)/count(distinct ucp.user_id) `avgTime`\n" +
            "from user_chapter_process ucp\n" +
            "left join chapter_info ci on ci.id = ucp.chapter_id\n" +
            "group by ci.chapter_name")
    List<Vo1> getList1();

    @Select("with a as (\n" +
            "    select\n" +
            "        case\n" +
            "            when score<60 then '[0,60)'\n" +
            "            when score>=60 and score<80 then '[60,80)'\n" +
            "            when score>=80 and score<90 then '[80,90)'\n" +
            "            when score>=90 and score<95 then '[90,95)'\n" +
            "            when score>=95 and score<=100 then '[95,100]'\n" +
            "            end `name`\n" +
            "    from test_exam\n" +
            ")\n" +
            "select name,count(*) `value` from a group by name")
    List<Vo> getList2();

    @Select("select\n" +
            "    bp.name,\n" +
            "    sum(oi.final_amount) `value`\n" +
            "from order_info oi\n" +
            "left join base_province bp on oi.province_id = bp.id\n" +
            "group by bp.name")
    List<Vo> getList3();

    @Select("select\n" +
            "    sum(final_amount)\n" +
            "from order_info")
    Double getList4();

    @Select("select\n" +
            "    hour(create_time) `name`,\n" +
            "    count(*) `value`\n" +
            "from user_chapter_process\n" +
            "group by hour(create_time)\n" +
            "order by hour(create_time)")
    List<Vo> getList5();

    @Select("select\n" +
            "    bs.source_site `name`,\n" +
            "    sum(oi.final_amount) `value`,\n" +
            "    count(distinct oi.user_id) `num1`,\n" +
            "    count(oi.id) `num2`\n" +
            "from order_info oi\n" +
            "left join order_detail od on oi.id = od.order_id\n" +
            "left join chapter_info ci on od.course_id = ci.course_id\n" +
            "left join video_info vi on ci.id = vi.chapter_id\n" +
            "left join base_source bs on vi.video_source_id = bs.id\n" +
            "group by bs.source_site")
    List<Vo2> getList6();

    @Select("select\n" +
            "    count(*)\n" +
            "from order_info\n" +
            "where date(create_time)='2021-04-23'")
    Double getList7();

    @Select("select\n" +
            "    ci.course_name `name`,\n" +
            "    sum(oi.final_amount) `value`\n" +
            "from order_info oi\n" +
            "left join order_detail od on oi.id = od.order_id\n" +
            "left join course_info ci on od.course_id = ci.id\n" +
            "group by ci.course_name")
    List<Vo> getList8();

    @Select("select\n" +
            "    hour(create_time) `name`,\n" +
            "    count(*) `value`\n" +
            "from user_chapter_process\n" +
            "group by hour(create_time)\n" +
            "order by hour(create_time)")
    List<Vo> getList9();

    @Select("select\n" +
            "    ci.course_name name,\n" +
            "    count(distinct te.user_id) `num`,\n" +
            "    avg(te.score) `avgScroe`,\n" +
            "    avg(te.duration_sec) `avgTime`\n" +
            "from test_exam te\n" +
            "left join test_paper tp on tp.id = te.paper_id\n" +
            "left join course_info ci on tp.course_id = ci.id\n" +
            "group by ci.course_name")
    List<Vo3> getList10();

    @Select("select\n" +
            "    count(*)\n" +
            "from payment_info\n" +
            "where date(create_time)='2021-04-23'")
    Double getList11();

    @Select("select\n" +
            "    ci.course_name `name`,\n" +
            "    avg(ri.review_stars) `avgStar`,\n" +
            "    count(distinct ri.user_id) `num1`,\n" +
            "    sum(if(ri.review_stars>=3,1,0)) `num2`,\n" +
            "    sum(if(ri.review_stars>=3,1,0))/count(distinct ri.user_id)*100 `lv`\n" +
            "from review_info ri\n" +
            "left join course_info ci on ci.id = ri.course_id\n" +
            "group by ci.course_name")
    List<Vo4> getList12();

    @Select("select\n" +
            "    hour(create_time) `name`,\n" +
            "    count(*) `value`\n" +
            "from user_chapter_process\n" +
            "group by hour(create_time)\n" +
            "order by hour(create_time)")
    List<Vo> getList13();

    @Select("select\n" +
            "    tqi.question_txt `name`,\n" +
            "    count(teq.id) `num1`,\n" +
            "    sum(teq.is_correct) `num2`,\n" +
            "    sum(teq.is_correct)/count(teq.id)*100 `value`\n" +
            "from test_exam_question teq\n" +
            "left join test_question_info tqi on tqi.id = teq.question_id\n" +
            "group by tqi.question_txt")
    List<Vo2> getList14();

    @Select("select\n" +
            "    tp.paper_title `name`,\n" +
            "    count(distinct te.user_id) `num`,\n" +
            "    avg(te.score) `avgScore`,\n" +
            "    avg(te.duration_sec) `avgTime`\n" +
            "from test_paper tp\n" +
            "right join test_exam te on tp.id = te.paper_id\n" +
            "group by tp.paper_title")
    List<Vo3> getList15();

    @Select("select\n" +
            "    bs.source_site `name`,\n" +
            "    count(oi.id) `value`\n" +
            "from order_info oi\n" +
            "left join order_detail od on oi.id = od.order_id\n" +
            "left join course_info ci on od.course_id = ci.id\n" +
            "left join chapter_info c on ci.id = c.course_id\n" +
            "left join video_info vi on c.id = vi.chapter_id\n" +
            "left join base_source bs on vi.video_source_id = bs.id\n" +
            "group by bs.source_site")
    List<Vo> getList16();
}
